const { Op } = require('sequelize')
const Post = require('../models/Post')
const Categorys = require('../models/Categorys')
const sequelize = require('../config/db')
const Tag = require('../models/Tags')
const dayjs = require('dayjs')
const customParseFormat = require('dayjs/plugin/customParseFormat')
const utc = require('dayjs/plugin/utc')
const timezone = require('dayjs/plugin/timezone')
const Views = require('../models/Views')
// 加载插件
dayjs.extend(customParseFormat)
dayjs.extend(utc)
dayjs.extend(timezone)

async function getMonthlyTagUsageORM() {
  const twelveMonthsAgo = new Date()
  twelveMonthsAgo.setMonth(twelveMonthsAgo.getMonth() - 12)

  return await Tag.findAll({
    attributes: [
      [sequelize.fn('DATE_FORMAT', sequelize.col('createdAt'), '%Y-%m'), 'dataTime'],
      [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
    ],
    where: {
      createdAt: { [Op.gte]: twelveMonthsAgo },
    },
    group: 'dataTime',
    order: [['dataTime', 'DESC']],
    raw: true,
  })
}

// 补全缺失月份（count为0）
function fillEmptyMonths(results) {
  const months = getRecentMonths() // 生成YYYY-MM数组
  return months.map((m) => results.find((r) => r.dataTime === m) || { dataTime: m, count: 0 })
  // sort((a, b) => new Date(b.dataTime) - new Date(a.dataTime));
}

// 计算，文章，分类，标签，访问量
async function calculateGrowthStats() {
  // 时间范围定义
  const currentMonthStart = dayjs().startOf('month').format('YYYY-MM-DD HH:mm:ss')
  const currentMonthEnd = dayjs().endOf('month').format('YYYY-MM-DD HH:mm:ss')
  const lastMonthStart = dayjs().subtract(1, 'month').startOf('month').format('YYYY-MM-DD HH:mm:ss')
  const lastMonthEnd = dayjs().subtract(1, 'month').endOf('month').format('YYYY-MM-DD HH:mm:ss')
  // 并行查询
  const [
    totalArticles,
    totalCategory,
    totalTags,
    totalViews,
    currentArticles,
    currentCategories,
    currentTags,
    currentViews,
    lastMonthArticles,
    lastMonthCategories,
    lastMonthTags,
    lastMonthViews,
  ] = await Promise.all([
    // 获取全部
    Post.count(),
    Categorys.count(),
    Tag.count(),
    Views.count(),
    // 本月数据（限制时间范围）
    Post.count({
      where: { createdAt: { [Op.between]: [currentMonthStart, currentMonthEnd] } },
    }),
    Categorys.count({
      where: { createdAt: { [Op.between]: [currentMonthStart, currentMonthEnd] } },
    }),
    Tag.count({
      where: { createdAt: { [Op.between]: [currentMonthStart, currentMonthEnd] } },
    }),
    Views.count({
      where: { createdAt: { [Op.between]: [currentMonthStart, currentMonthEnd] } },
    }),
    // 上月数据（正确范围：上个月1号 00:00:00 到 上个月最后一天 23:59:59）
    Post.count({
      where: { createdAt: { [Op.between]: [lastMonthStart, lastMonthEnd] } },
    }),
    Categorys.count({
      where: { createdAt: { [Op.between]: [lastMonthStart, lastMonthEnd] } },
    }),
    Tag.count({
      where: { createdAt: { [Op.between]: [lastMonthStart, lastMonthEnd] } },
    }),
    Post.sum('views', {
      where: { createdAt: { [Op.between]: [lastMonthStart, lastMonthEnd] } },
    }),
  ])

  // 返回结构化数据
  return {
    articles: {
      total: totalArticles,
      growth: calculateGrowth(currentArticles, lastMonthArticles || 0),
    },
    categories: {
      total: totalCategory,
      growth: calculateGrowth(currentCategories, lastMonthCategories || 0),
    },
    tags: {
      total: totalTags,
      growth: calculateGrowth(currentTags, lastMonthTags || 0),
    },
    views: {
      total: totalViews,
      growth: calculateGrowth(currentViews, lastMonthViews || 0),
    },
  }
}

/**
 * 计算环比增长率（带正负号）
 * @param {number} current - 当前期数值
 * @param {number} last - 上期数值
 * @returns {string} 带符号的百分比字符串（如 "+12.5%" / "-5.2%" / "0%"）
 */
function calculateGrowth(current, last) {
  // 情况1：两期均为0
  if (current === 0 && last === 0) return '0%'

  // 情况2：上期为0（避免除零错误）
  if (last === 0) return `+${Math.abs(current) > 0 ? '100' : '0'}%`

  // 正常计算
  const percent = (((current - last) / last) * 100).toFixed(1)
  return `${percent >= 0 ? '+' : ''}${percent}%` // 强制显示正号
}

// 获取最近7/30/60天发布文章数量
async function getMonthlyArticleCounts(m = 30) {
  // 生成包含今天的时间范围：从 (m-1) 天前的 00:00:00 到今天的 23:59:59
  const start = dayjs().subtract(m - 1, 'day').startOf('day').toDate()
  const end = dayjs().endOf('day').toDate()

  const results = await Post.findAll({
    attributes: [
      [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'], // 按天分组
      [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
    ],
    where: {
      createdAt: {
        [Op.between]: [start, end],
      },
    },
    // group 使用函数以兼容不同 DB，不依赖别名解析
    group: [sequelize.fn('DATE', sequelize.col('createdAt'))],
    order: [[sequelize.literal('date'), 'ASC']],
    raw: true,
  })

  // 补全缺失日期
  return fillMissingDates(results, m)
}

// 计算近7/30/60天访问趋势
async function getLast30DaysViews(m = 30) {
  // 生成包含今天的时间范围：从 (m-1) 天前的 00:00:00 到今天的 23:59:59
  const start = dayjs().subtract(m - 1, 'day').startOf('day').toDate()
  const end = dayjs().endOf('day').toDate()

  const results = await Views.findAll({
    attributes: [
      [sequelize.fn('DATE', sequelize.col('createdAt')), 'date'],
      [sequelize.fn('COUNT', sequelize.col('id')), 'count'],
    ],
    where: {
      createdAt: {
        [Op.between]: [start, end],
      },
    },
    group: [sequelize.fn('DATE', sequelize.col('createdAt'))],
    order: [[sequelize.literal('date'), 'ASC']],
    raw: true,
  })

  // 补全缺失日期
  return fillMissingDates(results, m)
}

// 获取最近几个月
function getRecentMonths(monthCount = 12, allowCrossYear = false) {
  const now = dayjs()
  const months = []

  for (let i = 0; i < monthCount; i++) {
    const date = allowCrossYear ? now.subtract(i, 'month') : now.startOf('year').add(i, 'month')
    months.push(date.format('YYYY-MM'))
  }

  return allowCrossYear ? months.reverse() : months
}

// 补全缺失日期（天数）
function fillMissingDates(data, days) {
  const dateMap = {}
  data.forEach((item) => (dateMap[item.date] = item.count))

  return Array.from({ length: days }, (_, i) => {
    const date = dayjs()
      .subtract(days - 1 - i, 'day')
      .format('YYYY-MM-DD')
    return {
      date,
      count: dateMap[date] || 0,
    }
  })
}

module.exports = {
  getMonthlyTagUsageORM,
  fillEmptyMonths,
  calculateGrowthStats,
  getMonthlyArticleCounts,
  getLast30DaysViews,
}
